using System;
using System.Collections.Generic;
using System.Text;
using System.Xml;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    public class SqlDataAccessHelper
    {
        #region ConnectString
        protected static String ConnectString
        {
            get
            {
                return ReadConnectString("ConnectionString.xml"); 
            }
        }
        public static String ReadConnectString(String file)
        {
            try
            {
                XmlDocument doc = new XmlDocument();
                doc.Load(file);
                XmlElement root = doc.DocumentElement;
                String ConnectString = root.InnerText;
                return ConnectString;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region ExcuteNoneQuery
        public static int ExcuteNoneQuery(string strSql,List<SqlParameter> sqlParams)
        {
            int n = 0;
            try
            {
                SqlConnection connect = new SqlConnection(ConnectString);
                connect.Open();
                try
                {
                    SqlCommand command = connect.CreateCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = strSql;
                    if (sqlParams != null)
                    {
                        foreach (SqlParameter param in sqlParams)
                        {
                            command.Parameters.Add(param);
                        }
                    }
                    n = command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw new Exception("Loi khi thuc hien lenh " + strSql + ex.ToString());
                }
                finally
                {
                    connect.Close();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return n;
        }
        #endregion

        #region ExcuteQuery
        public static DataTable ExcuteQuery(string strSql, List<SqlParameter> sqlParams)
        {
            DataTable dt = new DataTable();
            try
            {
                SqlConnection connect = new SqlConnection(ConnectString);
                connect.Open();
                SqlCommand command;
                try
                {
                    command = new SqlCommand(strSql, connect);
                    command.CommandType = CommandType.Text;
                    if (sqlParams != null)
                    {
                        foreach (SqlParameter param in sqlParams)
                        {
                            command.Parameters.Add(param);
                        }
                    }

                    SqlDataAdapter adapt = new SqlDataAdapter(command);

                    adapt.Fill(dt);
                }
                catch (Exception ex)
                {
                    throw new Exception("Loi khi truy van : " + strSql, ex);
                }
                finally
                {
                    connect.Close();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return dt;
        }
        public static DataTable ExcuteQuery(String strSql)
        {
            return ExcuteQuery(strSql,null);
        }
        #endregion

        #region Report
        public static DataSet Report(String strSQL,String TenBang)
        {
            SqlConnection con = new SqlConnection(ConnectString);
            con.Open();

            SqlCommand cmd = new SqlCommand(strSQL, con);

            DataSet ds = new DataSet();
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            adapter.Fill(ds, TenBang);

            return ds;
        }
        #endregion
    }
}
